-- 创建 SEQUENCE
CREATE SEQUENCE xxl_job_info_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE xxl_job_log_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE xxl_job_log_report_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE xxl_job_logglue_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE xxl_job_registry_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE xxl_job_group_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE xxl_job_user_seq START WITH 1 INCREMENT BY 1;

-- 创建表
CREATE TABLE xxl_job_info (
  id NUMBER(11) NOT NULL,
  job_group NUMBER(11) NOT NULL,
  job_desc VARCHAR2(255 CHAR) NOT NULL,
  add_time TIMESTAMP DEFAULT NULL,
  update_time TIMESTAMP DEFAULT NULL,
  author VARCHAR2(64 CHAR) DEFAULT NULL,
  alarm_email VARCHAR2(255 CHAR) DEFAULT NULL,
  schedule_type VARCHAR2(50 CHAR) DEFAULT 'NONE' NOT NULL,
  schedule_conf VARCHAR2(128 CHAR) DEFAULT NULL,
  misfire_strategy VARCHAR2(50 CHAR) DEFAULT 'DO_NOTHING' NOT NULL,
  executor_route_strategy VARCHAR2(50 CHAR) DEFAULT NULL,
  executor_handler VARCHAR2(255 CHAR) DEFAULT NULL,
  executor_param VARCHAR2(512 CHAR) DEFAULT NULL,
  executor_block_strategy VARCHAR2(50 CHAR) DEFAULT NULL,
  executor_timeout NUMBER(11) DEFAULT 0 NOT NULL,
  executor_fail_retry_count NUMBER(11) DEFAULT 0 NOT NULL,
  glue_type VARCHAR2(50 CHAR) NOT NULL,
  glue_source CLOB,
  glue_remark VARCHAR2(128 CHAR) DEFAULT NULL,
  glue_updatetime TIMESTAMP DEFAULT NULL,
  child_jobid VARCHAR2(255 CHAR) DEFAULT NULL,
  trigger_status NUMBER(1) DEFAULT 0 NOT NULL,
  trigger_last_time NUMBER(13) DEFAULT 0 NOT NULL,
  trigger_next_time NUMBER(13) DEFAULT 0 NOT NULL,
  PRIMARY KEY (id)
);

--CREATE OR REPLACE TRIGGER trg_xxl_job_info
--  BEFORE INSERT ON xxl_job_info
--  FOR EACH ROW
--BEGIN
--  SELECT xxl_job_info_seq.NEXTVAL INTO :NEW.id FROM dual;
--END;
--/

CREATE TABLE xxl_job_log (
  id NUMBER(20) NOT NULL,
  job_group NUMBER(11) NOT NULL,
  job_id NUMBER(11) NOT NULL,
  executor_address VARCHAR2(255 CHAR) DEFAULT NULL,
  executor_handler VARCHAR2(255 CHAR) DEFAULT NULL,
  executor_param VARCHAR2(512 CHAR) DEFAULT NULL,
  executor_sharding_param VARCHAR2(20 CHAR) DEFAULT NULL,
  executor_fail_retry_count NUMBER(11) DEFAULT 0 NOT NULL,
  trigger_time TIMESTAMP DEFAULT NULL,
  trigger_code NUMBER(11) DEFAULT NULL,
  trigger_msg CLOB,
  handle_time TIMESTAMP DEFAULT NULL,
  handle_code NUMBER(11) DEFAULT NULL,
  handle_msg CLOB,
  alarm_status NUMBER(1) DEFAULT 0 NOT NULL,
  PRIMARY KEY (id)
);

--CREATE OR REPLACE TRIGGER trg_xxl_job_log
--  BEFORE INSERT ON xxl_job_log
--  FOR EACH ROW
--BEGIN
--  SELECT xxl_job_log_seq.NEXTVAL INTO :NEW.id FROM dual;
--END;
--/

CREATE TABLE xxl_job_log_report (
  id NUMBER(11) NOT NULL,
  trigger_day TIMESTAMP DEFAULT NULL,
  running_count NUMBER(11) DEFAULT 0 NOT NULL,
  suc_count NUMBER(11) DEFAULT 0 NOT NULL,
  fail_count NUMBER(11) DEFAULT 0 NOT NULL,
  update_time TIMESTAMP DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE (trigger_day)
);

CREATE OR REPLACE TRIGGER trg_xxl_job_log_report
  BEFORE INSERT ON xxl_job_log_report
  FOR EACH ROW
BEGIN
  SELECT xxl_job_log_report_seq.NEXTVAL INTO :NEW.id FROM dual;
END;
/

CREATE TABLE xxl_job_logglue (
  id NUMBER(11) NOT NULL,
  job_id NUMBER(11) NOT NULL,
  glue_type VARCHAR2(50 CHAR) DEFAULT NULL,
  glue_source CLOB,
  glue_remark VARCHAR2(128 CHAR) NOT NULL,
  add_time TIMESTAMP DEFAULT NULL,
  update_time TIMESTAMP DEFAULT NULL,
  PRIMARY KEY (id)
);

CREATE OR REPLACE TRIGGER trg_xxl_job_logglue
  BEFORE INSERT ON xxl_job_logglue
  FOR EACH ROW
BEGIN
  SELECT xxl_job_logglue_seq.NEXTVAL INTO :NEW.id FROM dual;
END;
/

CREATE TABLE xxl_job_registry (
  id NUMBER(11) NOT NULL,
  registry_group VARCHAR2(50 CHAR) NOT NULL,
  registry_key VARCHAR2(255 CHAR) NOT NULL,
  registry_value VARCHAR2(255 CHAR) NOT NULL,
  update_time TIMESTAMP DEFAULT NULL,
  PRIMARY KEY (id)
);

CREATE OR REPLACE TRIGGER trg_xxl_job_registry
  BEFORE INSERT ON xxl_job_registry
  FOR EACH ROW
BEGIN
  SELECT xxl_job_registry_seq.NEXTVAL INTO :NEW.id FROM dual;
END;
/

CREATE INDEX i_g_k_v ON xxl_job_registry (registry_group, registry_key, registry_value);

CREATE TABLE xxl_job_group (
  id NUMBER(11) NOT NULL,
  app_name VARCHAR2(64 CHAR) NOT NULL,
  title VARCHAR2(12 CHAR) NOT NULL,
  address_type NUMBER(1) DEFAULT 0 NOT NULL,
  address_list CLOB,
  update_time TIMESTAMP DEFAULT NULL,
  PRIMARY KEY (id)
);

CREATE OR REPLACE TRIGGER trg_xxl_job_group
  BEFORE INSERT ON xxl_job_group
  FOR EACH ROW
BEGIN
  SELECT xxl_job_group_seq.NEXTVAL INTO :NEW.id FROM dual;
END;
/

CREATE TABLE xxl_job_user (
  id NUMBER(11) NOT NULL,
  username VARCHAR2(50 CHAR) NOT NULL,
  password VARCHAR2(50 CHAR) NOT NULL,
  role NUMBER(1) NOT NULL,
  permission VARCHAR2(255 CHAR) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE (username)
);

CREATE OR REPLACE TRIGGER trg_xxl_job_user
  BEFORE INSERT ON xxl_job_user
  FOR EACH ROW
BEGIN
  SELECT xxl_job_user_seq.NEXTVAL INTO :NEW.id FROM dual;
END;
/

CREATE TABLE xxl_job_lock (
  lock_name VARCHAR2(50 CHAR) NOT NULL,
  PRIMARY KEY (lock_name)
);

-- 插入数据
INSERT INTO xxl_job_group (id, app_name, title, address_type, address_list, update_time) VALUES (xxl_job_group_seq.NEXTVAL, 'fs-schedule-exec', '日程执行器', 0, NULL, TO_TIMESTAMP('2024-08-06 22:21:31', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO xxl_job_group (id, app_name, title, address_type, address_list, update_time) VALUES (xxl_job_group_seq.NEXTVAL, 'xxl-job-executor-sample', '示例执行器', 0, NULL, TO_TIMESTAMP('2024-08-06 22:21:31', 'YYYY-MM-DD HH24:MI:SS'));


INSERT INTO xxl_job_info (id, job_group, job_desc, add_time, update_time, author, alarm_email, schedule_type, schedule_conf, misfire_strategy, executor_route_strategy, executor_handler, executor_param, executor_block_strategy, executor_timeout, executor_fail_retry_count, glue_type, glue_source, glue_remark, glue_updatetime, child_jobid) VALUES (xxl_job_info_seq.NEXTVAL, 1, '测试任务1', TO_TIMESTAMP('2018-11-03 22:21:31', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2018-11-03 22:21:31', 'YYYY-MM-DD HH24:MI:SS'), 'XXL', '', 'CRON', '0 0 0 * * ? *', 'DO_NOTHING', 'FIRST', 'demoJobHandler', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', NULL, 'GLUE代码初始化', TO_TIMESTAMP('2018-11-03 22:21:31', 'YYYY-MM-DD HH24:MI:SS'), '');
INSERT INTO xxl_job_user (id, username, password, role, permission) VALUES (xxl_job_user_seq.NEXTVAL, 'admin', 'e10adc3949ba59abbe56e057f20f883e', 1, NULL);
INSERT INTO xxl_job_lock (lock_name) VALUES ('schedule_lock');

COMMIT;
